package service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;

import model.Student;
import model.User;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import dao.studentdao.StudentDao;
import dao.studentdao.StudentDaoImpl;
import dao.userdao.UserDao;
import dao.userdao.UserDaoImpl;

public class YearService {
   private UserDao userdao=new UserDaoImpl();
   private StudentDao studentdao=new StudentDaoImpl();
   public ArrayList<Student> importExcel(File file,String year) throws Exception{
	  ArrayList<Student> list=new ArrayList<Student>();
	   //excel依次包含学号，姓名，学年等三项
      HSSFWorkbook wrokbook = null;
	  try {
		   wrokbook = new HSSFWorkbook(new FileInputStream(file));
	  } catch (FileNotFoundException e) {
		e.printStackTrace();
	  } catch (IOException e) {
		e.printStackTrace();
	  }
       
       HSSFSheet sheet = wrokbook.getSheet("Sheet1");
       //获取到Excel文件中的所有行数
       int rows = sheet.getPhysicalNumberOfRows();
       //遍历行
       for (int i = 0; i < rows; i++) {
       	 // 读取左上端单元格
           HSSFRow row = sheet.getRow(i);
           if (row != null) {
           	//获取到Excel文件中的所有的列
               int cells = row.getPhysicalNumberOfCells();
               String value = "";   
               for (int j = 0; j < cells; j++) {
                   //获取到列的值­
                   HSSFCell cell = row.getCell(j);
                   if (cell != null) {
                       switch (cell.getCellType()) {
                             case HSSFCell.CELL_TYPE_FORMULA:
                             break;
                             case HSSFCell.CELL_TYPE_NUMERIC:
                                 java.text.DecimalFormat   formatter   =   new   java.text.DecimalFormat("########"); 
                                  String   str   =   formatter.format(cell.getNumericCellValue());
                                  value += str + ",";       
                                  //value += String.valueOf(cell.getNumericCellValue()) + ",";  
                             break;  
                             case HSSFCell.CELL_TYPE_STRING:
                                   value += cell.getStringCellValue() + ",";
                             break;
                             default:
                                   value += "0";
                             break;
                        }
                   }   
                    
               }
               // 将数据插入到数据库中
               String[] val = value.split(",");
               //添加学生信息
               Student student=new Student();
               student.setStu_id(val[0]);
               student.setName(val[1]);
               student.setYear(year);
               student.setRole_id(2);
               list.add(student);
               studentdao.addStudent(student);
               //创建学生登录帐号  初始化密码为学号
               User user=new User();
               user.setUser_id(val[0]);
               user.setPassword(val[0]);
               userdao.addUser(user);
           }
       }
      return list;
   }
}
